﻿using NfsCommon;
using NfsCommon.DbHelper;
using NfsModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NfsDAL
{
    public partial class SalesManDAL
    {
        public SalesManDAL() { }
        public DataTable GetSalesManBycondtion(int pageindex, int pagesize)
        {
            int startindex = (pageindex - 1) * pagesize;
            string strSql = string.Format(@"select top  " + pagesize + " * from (select ROW_NUMBER() over(order by id desc) as rownumber,id,name,code,createuser,valid,createusername,createdate  from t_salesman  where 1=1 and   valid=1 )");
            strSql += "  as a where a.rownumber>" + startindex + " ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public  salesman GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" id,name,code,valid,createusername ");
            strSql.Append(" from t_salesman ");
            strSql.Append(" where id=" + id + "");
            NfsModel.salesman model = new NfsModel.salesman();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public DataTable GetMaxCode()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select MAX(convert(int,code))+1 as code from [t_salesman] where valid='1' ");
           
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        } 
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.salesman DataRowToModel(DataRow row)
        {
            NfsModel.salesman model = new NfsModel.salesman();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                 
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["code"] != null)
                {
                    model.code = row["code"].ToString();
                }
                
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }

                if (row["createusername"] != null && row["createusername"].ToString() != "")
                {
                    model.createusername = row["createusername"].ToString();
                }
            }
            return model;
        }
        
        public object GetSalesManBycondtionCount()
        {
            string strSql = string.Format(@"select count(*) from t_salesman where  valid=1 ");

            return DbHelperMySQL.GetSingle(strSql);
        }
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,name,code,createuser,valid,createusername,createdate ");
            strSql.Append(" FROM t_salesman ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }
        
        /// <summary>
        /// 重复判断
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="coding"></param>
        /// <returns></returns>
        public bool GetSingle(int id, string name, string code)
        {

            StringBuilder strSql = new StringBuilder();
            if (id == 0)//表示添加
            {
                strSql.Append("select count(1) from t_salesman where valid=1 and (");
                strSql.Append(" name='" + name + "'");
                strSql.Append(" or code='" + code + "')");
            }
            else //表示添加
            {
                strSql.Append("select count(1) from t_salesman where valid=1 and (");
                strSql.Append(" name='" + name + "'");
                strSql.Append(" or code='" + code + "')");
                strSql.Append(" and id!=" + id + " ");
            }
            return DbHelperMySQL.Exists(strSql.ToString());

        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(NfsModel.salesman model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            
            if (model.name != null)
            {
                strSql1.Append("name,");
                strSql2.Append("'" + model.name + "',");
            }
            if (model.code != null)
            {
                strSql1.Append("code,");
                strSql2.Append("'" + model.code + "',");
            }
            if (model.createusername != null)
            {
                strSql1.Append("createusername,");
                strSql2.Append("'" + model.createusername + "',");
            }
            if (model.valid != null)
            {
                strSql1.Append("valid,");
                strSql2.Append("" + model.valid + ",");
            }

            strSql.Append("insert into t_salesman(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
               
                return true;
            }
            else
            {
                
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(NfsModel.salesman model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_salesman set ");
            
            if (model.name != null)
            {
                strSql.Append("name='" + model.name + "',");
            }

            if (model.code != null)
            {
                strSql.Append("code='" + model.code + "',");
            }

            if (model.valid != null)
            {
                strSql.Append("valid=" + model.valid + ",");
            }

            
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_salesman ");
            strSql.Append(" where id=" + id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
